<?php
		
	class bLinqSql extends bLinqStore implements IbLinqStore {
		
		private static $oConnection;
		private $sDebug;
		protected $aQuery;
		
		public function __construct($h=MYSQL_HOST, $u=MYSQL_USERNAME, $p=MYSQL_PASSWORD, $d=MYSQL_DATABASE) {
			
			$this->connect($h, $u, $p);
			$this->db($d); // hard coded.
		}
		
		public function __destruct() {
		
//			if(is_resource(self::$oConnection)) {
//				
//				mysql_close(self::$oConnection);
//			}
		}
		
		protected function connect($h =MYSQL_HOST, $u =MYSQL_USERNAME, $p =MYSQL_PASSWORD) {
			
			self::$oConnection 
				=mysql_connect($h, $u, $p);
		}
		
		public function db($sDb) {
			
			mysql_select_db($sDb, self::$oConnection);
		}
		
		
		public static function Not($sFields) {
			
			$oReturn =new StdClass();
			$oReturn->not =$sFields;
			
			return $oReturn;
		}
		
		private function Glue($aQuery) {
			
			if(empty($this->aQuery['where'])) { return; }
			if(strtolower((string)key(end($aQuery['where']))) =='or') {
				
				$this->aQuery['where'][] ='OR';
			} else {
				
				$this->aQuery['where'][] ='AND';
			}
		}
		
		private function SqlDebug($sSql =null) {
			
			if(isset($this->sDebug)) {
				
				$sError = mysql_error(self::$oConnection);
				$bError = empty($sError);
				
				switch($this->sDebug) {
				
					case 'dump':
						if(is_null($sSql)) { $sSql =__METHOD__; }
						var_dump($sSql, $sError);
					break;
					case 'file':
						file_put_contents('dump_bSql.txt', __METHOD__."\n".$sSql."\n".$sError."\n", FILE_APPEND);
					break;
				}
			}

		}
		
		public function Debug($oLinq, $sType) {
			
			$this->sDebug =$sType;
			
			return $oLinq;
		}
		
		public function Equal($oLinq, $mValue) {
		
			$aQuery =$oLinq->RawQuery();
			unset($aQuery['where']["="]);

			$aWhereKeys =array_keys($aQuery['where']);
			$sKey =end($aWhereKeys);

			$sValue = $mValue ===null ? 'IS NULL' :  "= '".mysql_escape_string($mValue)."'";
			$sWhere = "`".$sKey."` ".$sValue;
			if(isset($this->aQuery['where'][$sKey])) {
				
				$sKey =$sKey.count($this->aQuery['where']);
			}
			
			$this->Glue($aQuery);
			$this->aQuery['where'][$sKey] =$sWhere;
			
			return $oLinq;
		}
		
		public function NotEqual($oLinq, $mValue) {
		
			$aQuery =$oLinq->RawQuery();
			unset($aQuery['where']["<>"]);
			
			$sKey =end(array_keys($aQuery['where']));
			$sValue = $mValue ===null ? 'IS NOT NULL' :  "<> '".mysql_escape_string($mValue)."'";
			$sWhere = "`".$sKey."` ".$sValue;
			if(isset($this->aQuery['where'][$sKey])) {
				
				$sKey =$sKey.count($this->aQuery['where']);
			}
			
			$this->Glue($aQuery);
			$this->aQuery['where'][$sKey] =$sWhere;
			
			return $oLinq;
		}
		
		public function GreaterThan($oLinq, $mValue) {
			
			$aQuery =$oLinq->RawQuery();

			unset($aQuery['where'][">"]);
			
			$sKey =end(array_keys($aQuery['where']));
			$sWhere = "`".$sKey."` > '".mysql_escape_string($mValue)."'";
			if(isset($this->aQuery['where'][$sKey])) {
				
				$sKey =$sKey.count($this->aQuery['where']);
			}
			
			$this->Glue($aQuery);
			$this->aQuery['where'][$sKey] =$sWhere;			
			return $oLinq;
		}
		
		public function LessThan($oLinq, $mValue) {
			
			$aQuery =$oLinq->RawQuery();
			unset($aQuery['where']["<"]);
			
			$sKey =end(array_keys($aQuery['where']));
			$sWhere = "`".$sKey."` < '".mysql_escape_string($mValue)."'";
			if(isset($this->aQuery['where'][$sKey])) {
				
				$sKey =$sKey.count($this->aQuery['where']);
			}
			
			$this->Glue($aQuery);
			$this->aQuery['where'][$sKey] =$sWhere;
			
			return $oLinq;
		}
		
		public function StartsWith($oLinq, $mValue) {
			
			$aQuery =$oLinq->RawQuery();
			unset($aQuery['where']["-%"]);
			
			$sKey =end(array_keys($aQuery['where']));
			$sWhere = "`".$sKey."` LIKE '".mysql_escape_string($mValue)."%'";
			if(isset($this->aQuery['where'][$sKey])) {
				
				$sKey =$sKey.count($this->aQuery['where']);
			}
			
			$this->Glue($aQuery);
			$this->aQuery['where'][$sKey] =$sWhere;
			
			return $oLinq;
		}
		
		public function Contains($oLinq, $mValue) {	
			$aQuery =$oLinq->RawQuery();

			unset($aQuery['where']["%"]);
			
			$sKey =end(array_keys($aQuery['where']));
			$sWhere = "`".$sKey."` LIKE '%".mysql_escape_string($mValue)."%'";
			if(isset($this->aQuery['where'][$sKey])) {
				
				$sKey =$sKey.count($this->aQuery['where']);
			}
			
			$this->Glue($aQuery);
			$this->aQuery['where'][$sKey] =$sWhere;
			
			return $oLinq;
		}
		
		public function EndsWith($oLinq, $mValue) {
			
			$aQuery =$oLinq->RawQuery();
			unset($aQuery['where']["%-"]);
			
			$sKey =end(array_keys($aQuery['where']));
			$sWhere = "`".$sKey."` LIKE '%".mysql_escape_string($mValue)."'";
			if(isset($this->aQuery['where'][$sKey])) {
				
				$sKey =$sKey.count($this->aQuery['where']);
			}
			
			$this->Glue($aQuery);
			$this->aQuery['where'][$sKey] =$sWhere;
			
			return $oLinq;
		}
		
		public function Raw($oLinq, $sSql) {
			
			if(is_null(self::$oConnection)) { $this->connect(); }
			
			$oResult =new bLinqResult();
			$rResult =mysql_query($sSql, self::$oConnection);
			
			if(is_bool($rResult)) {
			
				$oResult['isValid'] =$bResult;
			} else {
			
				$oResult =new bLinqResult();
				while ($aRow = mysql_fetch_array($rResult, MYSQL_ASSOC)) {
   			
   					$oResult->Add($aRow);
				}
			}
			
			return $oResult;
		}
		
		public function Add($oLinq, $aInsert =null) {
			
			$aInsert =$oLinq->RawKeyValue();
			$aQuery =$oLinq->RawQuery();
			$sFrom ='`'.$aQuery['from'].'`';
			$sFields =self::AsCsv(array_keys($aInsert), '`');
			$sValues =self::AsCsv(array_values($aInsert));
			
			$sSql ="INSERT INTO {$sFrom} ($sFields) VALUES ($sValues)";
	
			if(is_null(self::$oConnection)) { $this->connect(); }
			
			$oResult =new bLinqResult();
			$bResult =mysql_query($sSql, self::$oConnection);
			$oResult['isValid'] =$bResult;
			$oResult['autoIncrement'] =mysql_insert_id(self::$oConnection);
			
			$this->SqlDebug($sSql);
			
			return $oResult;
		}
		
		public function Update($oLinq, $aUpdate =null) {
			
			$aUpdate =$oLinq->RawKeyValue();
			$aQuery =$oLinq->RawQuery();
			$sFrom ='`'.$aQuery['from'].'`';
			$sKeyValues =self::AsKeyValueString($aUpdate);
			
			$aQueryBase =isset($aQuery['where']) ? $aQuery['where'] : array();
			
			$aMerged =array_merge($this->aQuery['where']);
			unset($aMerged["="]);unset($aMerged["<"]);unset($aMerged[">"]);
			unset($aMerged["-%"]);unset($aMerged["%"]);unset($aMerged["%-"]);
			
			$sWhere =implode(' ', $aMerged);
			$sSql = "UPDATE {$sFrom} SET $sKeyValues WHERE {$sWhere}";
	
			if(is_null(self::$oConnection)) { $this->connect(); }
			
			$oResult =new bLinqResult();
			$bResult =mysql_query($sSql, self::$oConnection);
			$oResult['isValid'] =$bResult;
			
			$this->SqlDebug($sSql);
							
			return $oResult;
		}
		
		public function Merge($oLinq, $mMerge =null) {
			
			$aQuery =$oLinq->RawQuery();
			$aField =$oLinq->RawKeyValue();
			$aMergeKeys =$oLinq->MergeKeys();
			
			$bIsReverse =false;
			if(is_object($mMerge) && isset($mMerge->not)) {
				
				$bIsReverse =true;
				$mMerge =$mMerge->not;
			}
			
			$aMerge =array_map('trim', explode(',', $mMerge));
			$aMerge =array_merge((array)$aMerge, $aMergeKeys['keys']);
			
			$aWhere =$bIsReverse 
				? array_diff($aMerge, array_keys($aField)) 
				: array_diff(array_keys($aField), $aMerge);
			
			$this->aQuery['page'] =array(1, 1);
			$this->aQuery['where'] =array();
			foreach($aWhere as $sField) {
			
				$sValue = is_null($aField[$sField]) ? ' IS NULL' : " = '".mysql_escape_string($aField[$sField])."'";
				$this->aQuery['where'][$sField] ="`".$sField."`".$sValue;
				$this->aQuery['where'][] ='AND';
			}
		
			array_pop($this->aQuery['where']); // for that last AND
			
			$this->SqlDebug(); // so you know its a merge... before the add or update dump.	
			$oSelectResult =$this->Select($oLinq, '*');
			
			if($oSelectResult->Count() == 0) {
				
				$oResult =$this->Add($oLinq);
			} else {
				
				$oResult =$this->Update($oLinq);
			}
			
			$oSelectResultAfter =$this->Select($oLinq, '*');
			
			if(empty($aMergeKeys['format'])) {
			
				$oResult['changed'] =array_diff((array)(array)$oSelectResult->current(), (array)$oSelectResultAfter->current());
			} else {
				
				$aChanged =array();
				foreach($oSelectResult->current() as $sPreviousKey =>$sPreviousValue) {
					
					$oCurrentValue =$oSelectResultAfter->current();
					$sCurrentValue =$oCurrentValue[$sPreviousKey];
					if($sPreviousValue !=$sCurrentValue) {
						
						if(isset($aMergeKeys['format'][$sPreviousKey])) {
							
							$a = 1 + $b = 0; // normally won't continue
							switch($aMergeKeys['format'][$sPreviousKey]) {
								
								case bLinq::NUMERIC:
									
									$a =preg_replace('/\D/', '', $sPreviousValue);
									$b =preg_replace('/\D/', '', $sCurrentValue);
								break;
								case bLinq::CASE_INSENSITIVE:
								
									$a =strtolower($sPreviousValue);
									$b =strtolower($sCurrentValue);
								break;
							}
							
							if($a ==$b) { continue; }
						}
						
						$aChanged[$sPreviousKey] =$sCurrentValue;
					}
				}
				
				$oResult['changed'] =$aChanged;
			}
			return $oResult;
		}
		
		public function Delete($oLinq, $aDelete =null) {
			
			$aQuery =$oLinq->RawQuery();
			
			$sSql ='DELETE FROM '.$aQuery['from'];
			
			$aQueryBase =isset($aQuery[$sSequence]) ? $aQuery[$sSequence] : array();
			
			$aMerged =array_merge($aQueryBase, $this->aQuery['where']);
			unset($aMerged["="]);unset($aMerged["<"]);unset($aMerged[">"]);
			unset($aMerged["-%"]);unset($aMerged["%"]);unset($aMerged["%-"]);
						
			$sSql .=' WHERE '.implode(' ', $aMerged);
			
			if(is_null(self::$oConnection)) {
				
				$this->connect();
			}
			
			$oResult =new bLinqResult();
			$bResult =mysql_query($sSql, self::$oConnection);
			$oResult['isValid'] =true;
			
			$this->SqlDebug($sSql);
			
			return $oResult;
		}
		
		public function Select($oLinq, $aSelect =null) {
			
			$aQuery =$oLinq->RawQuery();
			
			if(is_string($aSelect)) { $aSelect =array($aSelect); }
			is_array($aSelect) or $aSelect =array('*');
			
			$aSql =array('SELECT '.self::AsCsv($aSelect, null));
			
			foreach(array('from', 'where', 'in', 'orderby', 'page') as $sSequence) {
				
				switch(true) {
					
					case $sSequence == 'from' && isset($aQuery[$sSequence]):
						$aSql[] =' FROM '.$aQuery[$sSequence];
					break;
					case $sSequence == 'where' && (isset($aQuery[$sSequence]) || isset($this->aQuery['where'])):
						
						$aQueryBase =isset($aQuery[$sSequence]) ? $aQuery[$sSequence] : array();		
						$aMerged = $this->aQuery['where']; // No MERGE of arrays needed because $this->Glue takes care of it now.

						unset($aMerged["="]);unset($aMerged["<"]);unset($aMerged[">"]);
						unset($aMerged["-%"]);unset($aMerged["%"]);unset($aMerged["%-"]);

						$aSql[] =' WHERE '.implode(' ', array_values($aMerged));
					break;
					case $sSequence == 'in' && isset($aQuery[$sSequence]):
						$aSql[] =' IN ('.$aQuery[$sSequence].')';
					break;
					case $sSequence == 'orderby' && isset($aQuery[$sSequence]):
						$sOrderBy =' ORDER BY ';
							foreach($aQuery[$sSequence] as $sField => $sDirection) {
								
								$aOrderBy[] =$sField.' '.$sDirection;
							}
						$aSql[] =$sOrderBy.implode(', ', $aOrderBy);
					break;
					case $sSequence == 'page' && isset($aQuery[$sSequence]):
						
						$nPerPage =$aQuery[$sSequence][1];
						$aSql[] =' LIMIT '.(($aQuery[$sSequence][0] - 1) * $nPerPage).', '.$nPerPage;
					break;
				}
			}
			
			if(is_null(self::$oConnection)) { $this->connect(); }
			
			$sSql =implode($aSql);
			
			$rResult =mysql_query($sSql, self::$oConnection);
			$oResult =new bLinqResult();
			while ($aRow = mysql_fetch_array($rResult, MYSQL_ASSOC)) {
   			
   				$oResult->Add($aRow);
			}
			
			if(isset($aQuery['page'])) {
				
				$aSql[0] = 'SELECT count(*) AS count';
				array_pop($aSql);
				$sSqlPageCount =implode($aSql);
				
				$rResult =mysql_query($sSqlPageCount, self::$oConnection);
				$aRow = mysql_fetch_array($rResult, MYSQL_ASSOC);
				
				$nCount =(int)(string)$aRow['count'];
				$oResult['numberOfPages'] =ceil($nCount/$nPerPage);
				$oResult['numberOfRecords'] =$nCount;
			}
			
			$this->SqlDebug($sSql);
			mysql_free_result($rResult);
			
			return $oResult;
		}
		
		public function Explain($oLinq) {
			
			$aQuery =$oLinq->RawQuery();
			$sFrom ='`'.$aQuery['from'].'`';
			$sSql = "EXPLAIN {$sFrom}";
			
			if(is_null(self::$oConnection)) {
				$this->connect();
			}
			
			$oResult =new bLinqResult();
			$rResult =mysql_query($sSql, self::$oConnection);
			while ($aRow = mysql_fetch_array($rResult, MYSQL_ASSOC)) {
   			
   				$oResult->Add($aRow);
			}
			
			if(isset($this->sDebug)) {
				
				switch($this->sDebug) {
				
					case 'dump':
						var_dump($sSql);
					break;
					case 'file':
						file_put_contents('dump_bSql.txt', __METHOD__."\n".$sSql."\n", FILE_APPEND);
					break;
				}
			}
			
			return $oResult;
		}
		
		public function __call($sKey, $sValue) {
		
			switch($sKey) {
				case '=':
					return call_user_func_array(array($this, 'Equal'), $sValue);
				break;
				case '<>':
					return call_user_func_array(array($this, 'NotEqual'), $sValue);
				break;
				case '>':
					return call_user_func_array(array($this, 'GreaterThan'), $sValue);
				break;
				case '<':
					return call_user_func_array(array($this, 'LessThan'), $sValue);
				break;
				case '%':
					return call_user_func_array(array($this, 'Contains'), $sValue);
				break;
				case '%-':
					return call_user_func_array(array($this, 'EndsWith'), $sValue);
				break;
				case '-%':
					return call_user_func_array(array($this, 'StartsWith'), $sValue);
				break;
			}
		}
	}
	
?>
